using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.SGI.ERP.Entidades;

namespace MakeIT.SGI.ERP.Negocios
{
	public partial class SgiDAOT : DAOT
	{

		public CtacorVO getCtacor(int? idCtaCor, string ctaCor)
		{
			CtacorVO ctacorVO = null;
			List<CtacorVO> listCtacorVOs = getCtacors(idCtaCor, ctaCor, null, null, null, null, null);
			if (listCtacorVOs != null && listCtacorVOs.Count > 0)
			{
				ctacorVO = listCtacorVOs[0];
			}
			return ctacorVO;
		}

		public CtacorVO getCtacor(int? idCtaCor)
		{
			return getCtacor(idCtaCor, null);
		}

		public List<CtacorVO> getCtacors()
		{
			return getCtacors(null, null, null, null, null, null, null);
		}

		public List<CtacorVO> getCtacors(int? idCtaCor)
		{
			return getCtacors(idCtaCor, null, null, null, null, null, null);
		}

		public List<CtacorVO> getCtacors(int? idCtaCor, string ctaCor)
		{
			return getCtacors(idCtaCor, ctaCor, null, null, null, null, null);
		}

		public List<CtacorVO> getCtacors(int? idCtaCor, string ctaCor, int? idFilial, int? idBanco, int? idCtaCorTipo, string agencia, string conta)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT ctc.idCtaCor as ctc_idCtaCor, ctc.ctaCor as ctc_ctaCor, ctc.idFilial as ctc_idFilial, fil.filial as fil_filial, ctc.idBanco as ctc_idBanco, bco.banco as bco_banco, ctc.agencia as ctc_agencia, ctc.conta as ctc_conta, ctc.idCtaCorTipo as ctc_idCtaCorTipo, cct.ctaCorTipo as cct_ctaCorTipo, ctc.cadastro as ctc_cadastro, ctc.cadastradoPor as ctc_cadastradoPor, ctc.edicao as ctc_edicao, ctc.editadoPor as ctc_editadoPor  ");
			query.AppendLine("FROM ctacor as ctc LEFT OUTER JOIN filial as fil on ctc.idFilial=fil.idFilial  LEFT OUTER JOIN banco as bco on ctc.idBanco=bco.idBanco  LEFT OUTER JOIN ctacortipo as cct on ctc.idCtaCorTipo=cct.idCtaCorTipo  ");
			if (idCtaCor != null)
			{
				query.AppendLine(where + "ctc.idCtaCor=@idCtaCor ");
				where = "AND ";
			}
			if (ctaCor != null)
			{
				query.AppendLine(where + "ctc.ctaCor LIKE @ctaCor ");
				where = "AND ";
			}
			if (idFilial != null)
			{
				query.AppendLine(where + "ctc.idFilial=@idFilial ");
				where = "AND ";
			}
			if (idBanco != null)
			{
				query.AppendLine(where + "ctc.idBanco=@idBanco ");
				where = "AND ";
			}
			if (agencia != null)
			{
				query.AppendLine(where + "ctc.agencia=@agencia ");
				where = "AND ";
			}
			if (conta != null)
			{
				query.AppendLine(where + "ctc.conta=@conta ");
				where = "AND ";
			}
			if (idCtaCorTipo != null)
			{
				query.AppendLine(where + "ctc.idCtaCorTipo=@idCtaCorTipo ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY ctc.ctaCor ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idCtaCor!= null) cmd.Parameters.Add(getParameter(cmd, "@idCtaCor", idCtaCor));
			if (ctaCor!= null) cmd.Parameters.Add(getParameter(cmd, "@ctaCor", "%"+ctaCor+"%"));
			if (idFilial!= null) cmd.Parameters.Add(getParameter(cmd, "@idFilial", idFilial));
			if (idBanco!= null) cmd.Parameters.Add(getParameter(cmd, "@idBanco", idBanco));
			if (agencia!= null) cmd.Parameters.Add(getParameter(cmd, "@agencia", agencia));
			if (conta!= null) cmd.Parameters.Add(getParameter(cmd, "@conta", conta));
			if (idCtaCorTipo!= null) cmd.Parameters.Add(getParameter(cmd, "@idCtaCorTipo", idCtaCorTipo));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<CtacorVO> listCtacor = new List<CtacorVO>();

				while (reader.Read())
				{
					CtacorVO ctacorVO = CtacorVO.CreateVO();
					ctacorVO.idCtaCor = reader.GetInt32(reader.GetOrdinal("ctc_idCtaCor"));
					ctacorVO.ctaCor = reader.GetString(reader.GetOrdinal("ctc_ctaCor"));
					ctacorVO.idFilial = reader.GetInt32(reader.GetOrdinal("ctc_idFilial"));
					if (!reader.IsDBNull(reader.GetOrdinal("fil_filial")))
						ctacorVO.idFilialVO.filial = reader.GetString(reader.GetOrdinal("fil_filial"));
					ctacorVO.idBanco = reader.GetInt32(reader.GetOrdinal("ctc_idBanco"));
					if (!reader.IsDBNull(reader.GetOrdinal("bco_banco")))
						ctacorVO.idBancoVO.banco = reader.GetString(reader.GetOrdinal("bco_banco"));
					ctacorVO.agencia = reader.GetString(reader.GetOrdinal("ctc_agencia"));
					ctacorVO.conta = reader.GetString(reader.GetOrdinal("ctc_conta"));
					ctacorVO.idCtaCorTipo = reader.GetInt32(reader.GetOrdinal("ctc_idCtaCorTipo"));
					if (!reader.IsDBNull(reader.GetOrdinal("cct_ctaCorTipo")))
						ctacorVO.idCtaCorTipoVO.ctaCorTipo = reader.GetString(reader.GetOrdinal("cct_ctaCorTipo"));
					ctacorVO.cadastro = reader.GetDateTime(reader.GetOrdinal("ctc_cadastro"));
					ctacorVO.cadastradoPor = reader.GetString(reader.GetOrdinal("ctc_cadastradoPor"));
					ctacorVO.edicao = reader.GetDateTime(reader.GetOrdinal("ctc_edicao"));
					ctacorVO.editadoPor = reader.GetString(reader.GetOrdinal("ctc_editadoPor"));


					listCtacor.Add(ctacorVO);
				}
				reader.Close();
				return listCtacor;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setCtacor(CtacorVO ctacorVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE ctacor ");
			query.AppendLine("SET ctaCor=@ctaCor, idFilial=@idFilial, idBanco=@idBanco, agencia=@agencia, conta=@conta, idCtaCorTipo=@idCtaCorTipo,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idCtaCor=@idCtaCor ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCtaCor", ctacorVO.idCtaCor));
			cmd.Parameters.Add(getParameter(cmd, "@ctaCor", ctacorVO.ctaCor));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", ctacorVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@idBanco", ctacorVO.idBanco));
			cmd.Parameters.Add(getParameter(cmd, "@agencia", ctacorVO.agencia));
			cmd.Parameters.Add(getParameter(cmd, "@conta", ctacorVO.conta));
			cmd.Parameters.Add(getParameter(cmd, "@idCtaCorTipo", ctacorVO.idCtaCorTipo));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", ctacorVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", ctacorVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", ctacorVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", ctacorVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newCtacor(CtacorVO ctacorVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO ctacor ");
			query.AppendLine("  ( ctaCor, idFilial, idBanco, agencia, conta, idCtaCorTipo, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @ctaCor, @idFilial, @idBanco, @agencia, @conta, @idCtaCorTipo, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@ctaCor", ctacorVO.ctaCor));
			cmd.Parameters.Add(getParameter(cmd, "@idFilial", ctacorVO.idFilial));
			cmd.Parameters.Add(getParameter(cmd, "@idBanco", ctacorVO.idBanco));
			cmd.Parameters.Add(getParameter(cmd, "@agencia", ctacorVO.agencia));
			cmd.Parameters.Add(getParameter(cmd, "@conta", ctacorVO.conta));
			cmd.Parameters.Add(getParameter(cmd, "@idCtaCorTipo", ctacorVO.idCtaCorTipo));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", ctacorVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", ctacorVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", ctacorVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", ctacorVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('ctacor') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delCtacor(CtacorVO ctacorVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM ctacor ");
			query.AppendLine("WHERE idCtaCor=@idCtaCor ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCtaCor", ctacorVO.idCtaCor));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountCtacorxIdFilial(int? idFilial)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idFilial) as ocorrencias FROM ctacor WHERE IdFilial=@IdFilial ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdFilial", idFilial));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountCtacorxIdBanco(int? idBanco)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idBanco) as ocorrencias FROM ctacor WHERE IdBanco=@IdBanco ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdBanco", idBanco));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public int getCountCtacorxIdCtaCorTipo(int? idCtaCorTipo)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCtaCorTipo) as ocorrencias FROM ctacor WHERE IdCtaCorTipo=@IdCtaCorTipo ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCtaCorTipo", idCtaCorTipo));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
